Sometimes, while searching the root cause of database problems, we face page IDs and need to find to which object that page belongs.
We can find this information using DBCC Page, but it’s a manual procedure, we need to execute this command and look the text result to find the object id. This would be enough if we were looking for only one page id, but how to solve this problem when we are looking for several page ids ?
We can use ‘with tableresults’ clause with the DBCC Page statement to get the results in a table format, but we need to filter the result, so we need to use insert/exec to insert the result in a temporary table and then filter the result.
The insert/exec instruction can’t be used inside a function, so we will need to create one stored procedure that will receive a table parameter with the page ids that we need to transform in object names.
To use table parameter in a stored procedure we need first to create a table user defined table and them use this new user defined type to create the stored procedure.
We will create the user defined type with the following code:
|
1 2 3 4 5 |
create type PageIDs as TABLE (database_id int, file_id int, page_id varchar(30) ) go |
Now we can create the stored procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
Create procedure GetObjectNames @Tab PageIDs READONLY -- we use the user defined type as set nocount on -- Create a temp table with one more field -- objectName select database_id,file_id,page_id, cast('' as varchar(100)) as objectName into #tmpResult from @Tab declare @database_id int declare @file_id int declare @page_id varchar(20) declare @sql varchar(100) declare @objName varchar(100) -- Temp table variable for the insert/exec declare @tabtmp table (parentObject varchar(100), [Object] varchar(150), Field varchar(100), value varchar(100) ) -- cursor over the temp table -- we need to execute the dbcc page -- for each row in our temp table declare ct cursor for select database_id,file_id,page_id from #tmpResult open ct fetch next from ct into @database_id,@file_id,@page_id while @@FETCH_STATUS=0 begin -- DBCC Page is built as string select @sql='DBCC PAGE(' + cast(@database_id as varchar(100)) + ',' + cast(@file_id as varchar(100)) + ',' + @page_id + ') with tableresults' -- insert into temp table variable -- the executio of the string insert into @tabtmp exec(@sql) -- Retrieve from the temp table variable -- the object name of this page select @objName=object_name(value,@database_id) from @tabtmp where field='Metadata: ObjectId' -- Update the temp table with the object name update #tmpResult set objectName=@objName where current of ct -- Clear the temp variable and -- get the next record delete @tabtmp fetch next from ct into @database_id,@file_id,@page_id end close ct deallocate ct -- Return the result select * from #tmpResult |
The way to use the stored procedure can be quite different depending on the source of the page ids, but one simple example will be like this :
|
1 2 3 4 5 6 7 8 |
declare @t PageIDs insert into @t values (20,1,'64424'), (20,1,'10224'), (20,1,'50851') exec GetObjectNames @t |
Load comments